#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 2018/12/8 15:58
# @Author : Mr.L
# @File : D:\youjia201811\youjia_report_new\com\youjia\report\yy_city_org_center_investor.py

# 分城市门店投资人

import sys
import pandas as pd

import datetime

# sys.path.append('D:/youjia201811/youjia_report_new/com/youjia/util')
sys.path.append('/data/online/youjia_script/com/youjia/util')

from db_config import get_config_13, get_config_test_69, get_config_13_1, get_config_51
import logging
import calendar
import numpy as np
from send_util import sendBigEmail

logging.basicConfig()
LOG = logging.getLogger('toexcel')
LOG.setLevel(logging.DEBUG)
# 创建一个handler，用于写入日志文件
fh = logging.FileHandler('toexcel.log')
fh.setLevel(logging.DEBUG)
# 定义handler的输出格式
LOG_FORMAT = logging.Formatter('%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
fh.setFormatter(LOG_FORMAT)
# 给logger添加handler
LOG.addHandler(fh)

# 再创建一个handler，用于输出到控制台
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
ch.setFormatter(LOG_FORMAT)
LOG.addHandler(ch)

import pub


# 分城市投资人
def base_z():
    return '''
        SELECT  m.city_id,m.sid,concat(m.invest_id) invest_id ,m.short_name,m.org_center,m.investor,COUNT(0) lodge_num FROM(
        SELECT h.city_id,c.short_name short_name,usc.sid,usc.sname org_center,concat(ico.invest_id,'') invest_id ,u.realname investor ,concat(h.id,'') house_id ,concat(h.lodge_id,'') lodge_id 
        FROM invest_compact_house ico
        LEFT JOIN lod_house h ON ico.house_id=h.id
        LEFT JOIN user_org_service_center usc ON usc.sid=h.service_id
        -- LEFT JOIN view_lodge_base b ON  b.house_id=ico.house_id
        LEFT JOIN city c ON h.city_id=c.id
        LEFT JOIN users u ON ico.invest_id=u.id
        WHERE h.city_id<>376
        AND h.service_id IS NOT NULL
        AND ico.invest_id IS NOT NULL
        AND h.state  IN (100,110)
        AND h.lodge_id IS  NOT  NULL 
        AND h.lodge_id <>''
        AND  c.id<>376
        AND  h.id NOT in (250780301440089343,253299308349128492)
        GROUP BY h.id
        ORDER BY h.city_id
         )m      
        GROUP BY m.city_id,m.sid,m.invest_id
    '''


def getTarget_Each_z(st, month_range, ri, dt, et):
    return '''
                SELECT kk.city_id,kk.sid,concat(kk.invest_id,'') invest_id,
                # sum(if(kk.GMV>=kk.target_GMV10,1,0)) fair_num10 ,
                # sum(if(kk.GMV>=kk.target_GMV15,1,0)) fair_num15,
                # sum(if(kk.GMV>=kk.target_GMV20,1,0)) fair_num20,
                sum( fair_num10) fair_num10,
                sum( fair_num15) fair_num15,
                sum(fair_num20) fair_num20,
                round(SUM(kk.GMV)) GMV,round(SUM(kk.target_GMV10)) target_GMV10,round(sum(kk.target_GMV15)) target_GMV15,round(sum(kk.target_GMV20)) target_GMV20,round(sum(kk.rental)) rental

                FROM(
                    SELECT mm.city_id,mm.sid,mm.invest_id,SUM(mm.GMV) GMV,sum(mm.target_GMV10) target_GMV10,sum(mm.target_GMV15) target_GMV15,sum(mm.target_GMV20) target_GMV20,sum(mm.rental) rental,
                     if(SUM(mm.GMV)>sum(mm.target_GMV10),1,0)  fair_num10,  if(SUM(mm.GMV)>sum(mm.target_GMV15),1,0)  fair_num15,if(SUM(mm.GMV)>sum(mm.target_GMV20),1,0)  fair_num20
                     FROM(
                        SELECT k.city_id,k.sid,k.invest_id,k.house_id,
                        sum(k.GMV) GMV,sum(k.target_GMV10) target_GMV10,sum(k.target_GMV15) target_GMV15,sum(k.target_GMV20) target_GMV20,sum(k.target_GMV10) rental
                        # sum(k.GMV) GMV,((sum(k.act_days)/SUM(k.rent_days))*sum(k.target_GMV10)) target_GMV10,sum(k.target_GMV10) rental,
                        #  ((sum(k.act_days)/SUM(k.rent_days))*sum(k.target_GMV15)) target_GMV15,
                        #  ((sum(k.act_days)/SUM(k.rent_days))*sum(k.target_GMV20)) target_GMV20
                         FROM(
                        SELECT m.city_id,m.sid,m.invest_id,m.house_id,n.rent_days,n.act_days,if(pp.GMV IS NOT null,pp.GMV,0) GMV,qq.target_GMV10,qq.target_GMV15,qq.target_GMV20  FROM
                        (
                        -- 房屋
                        SELECT h.city_id city_id,h.service_id sid,c.short_name short_name,usc.sname org_center,concat(ico.invest_id,'') invest_id ,u.realname investor ,concat(h.id,'') house_id ,concat(h.lodge_id,'') lodge_id 
                                FROM invest_compact_house ico
                                LEFT JOIN lod_house h ON ico.house_id=h.id
                                LEFT JOIN user_org_service_center usc ON usc.sid=h.service_id
                                -- LEFT JOIN view_lodge_base b ON  b.house_id=ico.house_id
                                LEFT JOIN city c ON h.city_id=c.id
                                LEFT JOIN users u ON ico.invest_id=u.id
                                WHERE h.city_id<>376
                                AND h.service_id IS NOT NULL
                                AND ico.invest_id IS NOT NULL
                                AND h.state  IN (100,110)
                                AND h.lodge_id IS  NOT  NULL 
                                AND h.lodge_id <>''
                                AND  c.id<>376
                                AND  h.id NOT in (250780301440089343,253299308349128492)
                                GROUP BY h.id
                        ) m 
                        LEFT JOIN
                        -- 系数
                         (SELECT
                                  concat(lod_house_id,'') house_id, 
                                  concat(create_time,'') frist_Online_time,
                                  if(date(create_time)<'%s','%s',to_days(date(DATE_ADD('%s',INTERVAL 1 DAY))) - to_days(DATE(create_time))) rent_days,
                                  if(date(create_time)<'%s','%s',to_days(date(DATE_ADD('%s',INTERVAL 1 DAY))) - to_days(DATE(create_time))) act_days
                            FROM
                                  lod_house_online_time
                            WHERE

                                   sales_channel_id IS NULL
                                    OR sales_channel_id = 4
                                    GROUP BY lod_house_id
                            ORDER BY
                            create_time ASC
                        )n ON m.house_id=n.house_id
                        LEFT JOIN 
                        (
                        -- 收益 
                        SELECT concat(p.lod_house_id,'') house_id,SUM(p.`间夜`*p.price) GMV FROM(
                                        SELECT m.order_id,CONCAT(m.house_id) lod_house_id, (TO_DAYS(m.`离店时间`)-TO_DAYS(m.`入住时间`))*m.bookroom_num 间夜 ,n.price FROM(
                                        SELECT b.order_id,b.house_id,if(date(b.checkin_day)<='%s' AND date(b.checkout_day)>='%s' ,
                                                                    '%s',if(date(b.checkin_day)>='%s' AND date(b.checkin_day)<='%s',date(b.checkin_day),0) ) 入住时间
                                                                ,if(DATE(b.checkout_day)>'%s' AND DATE(b.checkout_day)<='%s',DATE(b.checkout_day),IF(DATE(b.checkout_day)>'%s',
                                                                    date_sub('%s',interval -1 day),0)  ) 离店时间,b.bookroom_num
                                                                FROM view_book_order b
                                                                WHERE  b.pay_state BETWEEN 0 AND 1
                                                                AND b.confirm_status=1
                                                                and b.cancel_status=0
                                                                AND b.city_id<>376
                                                                AND b.brush_status<>1
                                                                AND date(b.checkout_day)>'%s'  AND date(b.checkin_day)<='%s'
                                        )m
                                        LEFT JOIN 
                                                        (SELECT b1.order_id,b1.real_income/100/((TO_DAYS(b1.checkout_day)-TO_DAYS(b1.checkin_day))*b1.bookroom_num) price FROM view_book_order b1
                                                         WHERE b1.city_id<>376
                                                         and b1.pay_state BETWEEN 0 AND 1
                                                         AND b1.confirm_status=1
                                                         and b1.cancel_status=0
                                                         AND b1.brush_status<>1
                                                         )n ON m.order_id=n.order_id
                                        )p
                                        GROUP BY p.lod_house_id
                        )pp  ON m.house_id=pp.house_id
                        LEFT JOIN 
                        (
                        -- 目标
                         SELECT
                                CONCAT(aa.房屋id,'') house_id,
                                -- aa.房源id,
                                -- aa.城市,
                                -- aa.投资人姓名,
                                -- aa.居室,
                                -- aa.合同期限,
                                ROUND(aa.第一年+物业费+取暖费+网费,2)*(if(aa.days IS NOT NULL,aa.days,0)/30) target_GMV10, ROUND(aa.第一年+物业费+取暖费+网费,2)*(if(aa.days IS NOT NULL,aa.days,0)/30)*1.5 'target_GMV15',
                                ROUND(aa.第一年+物业费+取暖费+网费,2)*(if(aa.days IS NOT NULL,aa.days,0)/30)*2.0 'target_GMV20'
                                -- FORMAT(aa.第二年+物业费+取暖费+网费,2) 第二年,
                                -- FORMAT(aa.第三年+物业费+取暖费+网费,2) 第三年,
                                -- FORMAT(aa.第四年+物业费+取暖费+网费,2) 第四年,
                                -- FORMAT(IF(aa.合同期限>=5,aa.第五年+物业费+取暖费+网费,0),2) 第五年,
                                -- FORMAT(IF(aa.已签约日价>0,aa.已签约日价,aa.测算日价),0) 日价,
                                -- aa.回报周期
                                FROM(
                                SELECT 
                                ce.house_id 房屋id,
                                lo.lodge_id 房源id,
                                c.city_name 城市,
                                u.realname 投资人姓名,
                                lo.room_num 居室,
                                YEAR(ce.lease_date_end)-YEAR(ce.lease_date_begin) 合同期限,
                                SUBSTRING_INDEX(CONCAT(ce.rent_grow_ratio,',0'),',', 1) 第一年,
                                REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(ce.rent_grow_ratio,',',2)),',',1)) 第二年,
                                REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(ce.rent_grow_ratio,',',3)),',',1)) 第三年,
                                REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(ce.rent_grow_ratio,',',4)),',',1)) 第四年,
                                REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(CONCAT(ce.rent_grow_ratio,',0'),',',5)),',',1)) 第五年,
                                ce.agency_fee/100 中介费,
                                ce.property_fee/100/12 物业费,
                                ce.heating_fee/100/12 取暖费,
                                ce.net_fee/100/12 网费,
                                ce.forecast_cycle 回报周期,
                                ce.day_price 已签约日价,
                                inv.shop_day_price/100 测算日价,
                                lho.create_time,if(date(lho.create_time)<'%s','%s',DATEDIFF(date(DATE_ADD('%s',INTERVAL 1 DAY)),lho.create_time) )days
                                FROM invest_compact_house ce
                                LEFT JOIN invest_compact_estimate inv ON ce.house_id=inv.house_id
                                LEFT JOIN city c ON ce.`city_id`=c.`id`
                                LEFT JOIN users u ON ce.invest_id=u.id
                                LEFT JOIN lod_house lo ON ce.house_id=lo.id
                                LEFT JOIN (
				                            SELECT
                                                lod_house_id,create_time
                                                FROM
                                                    lod_house_online_time
                                                WHERE

                                                    sales_channel_id IS NULL
                                                    OR sales_channel_id = 4
                                                GROUP BY lod_house_id
                                                ORDER BY
                                                create_time ASC
                                                    )lho ON ce.house_id=lho.lod_house_id
                                WHERE ce.`state`=10
                                AND ce.`city_id`<>376
                                and date(lho.create_time)<='%s'
                                -- and SUBSTRING_INDEX(CONCAT(ce.rent_grow_ratio,',0'),',', 1) is not null
                                -- and u.realname is not null
                                )aa
                                -- where aa.房屋id=247084194319071428
                                ORDER BY aa.合同期限
                        )qq ON m.house_id=qq.house_id
                        GROUP BY m.house_id
                        )k
                        GROUP BY k.house_id
            )mm
            GROUP BY mm.city_id,mm.sid,mm.invest_id,mm.house_id
            )kk
            GROUP BY kk.city_id,kk.sid,kk.invest_id

    ''' % (st, month_range, et, st, ri, dt, st, st, st, st, dt, st, dt, dt, dt, st, dt, st, ri, dt, dt)


# 分城市门店投资人
def base():
    return '''
       SELECT concat(m.city_id,'') city_id,concat(m.sid,'') sid,concat(m.invest_id,'') invest_id,m.short_name,m.org_center,m.investor,COUNT(0)  lodge_num FROM (
        SELECT h.city_id,c.short_name short_name,usc.sid,usc.sname org_center,concat(ico.invest_id,'') invest_id ,u.realname investor ,concat(h.id,'') house_id ,concat(h.lodge_id,'') lodge_id 
        FROM invest_compact_house ico
        LEFT JOIN lod_house h ON ico.house_id=h.id
        LEFT JOIN user_org_service_center usc ON usc.sid=h.service_id
        -- LEFT JOIN view_lodge_base b ON  b.house_id=ico.house_id
        LEFT JOIN city c ON h.city_id=c.id
        LEFT JOIN users u ON ico.invest_id=u.id
        WHERE h.city_id<>376
        AND h.service_id IS NOT NULL
        AND ico.invest_id IS NOT NULL
        AND h.state  IN (100,110)
        AND h.lodge_id IS  NOT  NULL 
        AND h.lodge_id <>''
        AND  c.id<>376
        AND  h.id NOT in (250780301440089343,253299308349128492)
        GROUP BY h.id
        )m
         GROUP BY m.city_id,m.sid,m.invest_id;
    '''


def getOnLine_time(st, days, et, dt, ri):
    return '''
    SELECT concat(m.city_id,'') city_id,concat(m.sid,'') sid,concat(m.invest_id,'') invest_id,sum(n.rent_days) rent_days,SUM(n.act_days) act_days FROM(
        SELECT h.city_id,c.short_name short_name,usc.sid,usc.sname org_center,concat(ico.invest_id,'') invest_id ,u.realname investor ,concat(h.id,'') house_id ,concat(h.lodge_id,'') lodge_id 
                FROM invest_compact_house ico
                LEFT JOIN lod_house h ON ico.house_id=h.id
                LEFT JOIN user_org_service_center usc ON usc.sid=h.service_id
                -- LEFT JOIN view_lodge_base b ON  b.house_id=ico.house_id
                LEFT JOIN city c ON h.city_id=c.id
                LEFT JOIN users u ON ico.invest_id=u.id
                WHERE h.city_id<>376
                AND h.service_id IS NOT NULL
                AND ico.invest_id IS NOT NULL
                AND h.state  IN (100,110)
                AND h.lodge_id IS  NOT  NULL 
                AND h.lodge_id <>''
                AND  c.id<>376
                AND  h.id NOT in (250780301440089343,253299308349128492)
                GROUP BY h.id
        )m 
        LEFT JOIN (

            SELECT
                                            concat(lod_house_id,'') house_id, 
                                            concat(create_time,'') frist_Online_time,
                                            if(date(create_time)<'%s','%s',to_days(date(DATE_ADD('%s',INTERVAL 1 DAY))) - to_days(DATE(create_time))) rent_days,
                                            if(date(create_time)<'%s','%s',to_days(date(DATE_ADD('%s',INTERVAL 1 DAY))) - to_days(DATE(create_time))) act_days
                                FROM
                                            lod_house_online_time
                                WHERE

                                             sales_channel_id IS NULL
                                                OR sales_channel_id = 4
                                                GROUP BY lod_house_id
                                ORDER BY
                                create_time ASC
         )n ON m.house_id=n.house_id
                GROUP BY m.city_id,m.sid,m.invest_id
    ''' % (st, days, et, st, ri, dt)


def getinvestor_GMV(st, dt):
    return '''
       SELECT concat(m.city_id,'') city_id,concat(m.sid,'') sid,concat(m.invest_id,'') invest_id,sum(n.GMV) GMV FROM(
        SELECT h.city_id,c.short_name short_name,usc.sid,usc.sname org_center,concat(ico.invest_id,'') invest_id ,u.realname investor ,concat(h.id,'') house_id ,concat(h.lodge_id,'') lodge_id 
                FROM invest_compact_house ico
                LEFT JOIN lod_house h ON ico.house_id=h.id
                LEFT JOIN user_org_service_center usc ON usc.sid=h.service_id
                -- LEFT JOIN view_lodge_base b ON  b.house_id=ico.house_id
                LEFT JOIN city c ON h.city_id=c.id
                LEFT JOIN users u ON ico.invest_id=u.id
                WHERE h.city_id<>376
                AND h.service_id IS NOT NULL
                AND ico.invest_id IS NOT NULL
                AND h.state  IN (100,110)
                AND h.lodge_id IS  NOT  NULL 
                AND h.lodge_id <>''
                AND  c.id<>376
                AND  h.id NOT in (250780301440089343,253299308349128492)
                GROUP BY h.id
        )m 
        LEFT JOIN (
        SELECT concat(p.lod_house_id,'') house_id,SUM(p.`间夜`*p.price) GMV FROM(
                        SELECT m.order_id,CONCAT(m.house_id) lod_house_id, (TO_DAYS(m.`离店时间`)-TO_DAYS(m.`入住时间`))*m.bookroom_num 间夜 ,n.price FROM(
                        SELECT b.order_id,b.house_id,if(date(b.checkin_day)<='%s' AND date(b.checkout_day)>='%s' ,
                                                    '%s',if(date(b.checkin_day)>='%s' AND date(b.checkin_day)<='%s',date(b.checkin_day),0) ) 入住时间
                                                ,if(DATE(b.checkout_day)>'%s' AND DATE(b.checkout_day)<='%s',DATE(b.checkout_day),IF(DATE(b.checkout_day)>'%s',
                                                    date_sub('%s',interval -1 day),0)  ) 离店时间,b.bookroom_num
                                                FROM view_book_order b
                                                WHERE  b.pay_state BETWEEN 0 AND 1
                                                AND b.confirm_status=1
                                                and b.cancel_status=0
                                                AND b.city_id<>376
                                                AND b.brush_status<>1
                                                AND date(b.checkout_day)>'%s'  AND date(b.checkin_day)<='%s'
                        )m
                        LEFT JOIN 
                                        (SELECT b1.order_id,b1.real_income/100/((TO_DAYS(b1.checkout_day)-TO_DAYS(b1.checkin_day))*b1.bookroom_num) price FROM view_book_order b1
                                         WHERE b1.city_id<>376
                                         and b1.pay_state BETWEEN 0 AND 1
                                         AND b1.confirm_status=1
                                         and b1.cancel_status=0
                                         AND b1.brush_status<>1
                                         )n ON m.order_id=n.order_id
                        )p
                        GROUP BY p.lod_house_id
        )n ON m.house_id=n.house_id
        GROUP BY m.city_id,m.sid,m.invest_id


    ''' % (st, st, st, st, dt, st, dt, dt, dt, st, dt)


def getTarget_GMV():
    return '''
              SELECT concat(m.city_id,'') city_id,concat(m.sid,'') sid,concat(m.invest_id,'') invest_id,sum(n.target_GMV) target_GMV, sum(n.target_GMV15) target_GMV15,sum(n.target_GMV18) target_GMV18 FROM(
                SELECT h.city_id,c.short_name short_name,usc.sid,usc.sname org_center,concat(ico.invest_id,'') invest_id ,u.realname investor ,concat(h.id,'') house_id ,concat(h.lodge_id,'') lodge_id 
                        FROM invest_compact_house ico
                        LEFT JOIN lod_house h ON ico.house_id=h.id
                        LEFT JOIN user_org_service_center usc ON usc.sid=h.service_id
                        -- LEFT JOIN view_lodge_base b ON  b.house_id=ico.house_id
                        LEFT JOIN city c ON h.city_id=c.id
                        LEFT JOIN users u ON ico.invest_id=u.id
                        WHERE h.city_id<>376
                        AND h.service_id IS NOT NULL
                        AND ico.invest_id IS NOT NULL
                        AND h.state  IN (100,110)
                        AND h.lodge_id IS  NOT  NULL 
                        AND h.lodge_id <>''
                        AND  c.id<>376
                        AND  h.id NOT in (250780301440089343,253299308349128492)
                        GROUP BY h.id
                )m 
                LEFT JOIN (

        SELECT
                CONCAT(aa.房屋id,'') house_id,
                -- aa.房源id,
                -- aa.城市,
                -- aa.投资人姓名,
                -- aa.居室,
                -- aa.合同期限,
                ROUND(aa.第一年+物业费+取暖费+网费,2) target_GMV, ROUND(aa.第一年+物业费+取暖费+网费,2)*1.5 'target_GMV15',
                ROUND(aa.第一年+物业费+取暖费+网费,2)*2.33 'target_GMV18'
                -- FORMAT(aa.第二年+物业费+取暖费+网费,2) 第二年,
                -- FORMAT(aa.第三年+物业费+取暖费+网费,2) 第三年,
                -- FORMAT(aa.第四年+物业费+取暖费+网费,2) 第四年,
                -- FORMAT(IF(aa.合同期限>=5,aa.第五年+物业费+取暖费+网费,0),2) 第五年,
                -- FORMAT(IF(aa.已签约日价>0,aa.已签约日价,aa.测算日价),0) 日价,
                -- aa.回报周期
                FROM(
                SELECT 
                ce.house_id 房屋id,
                lo.lodge_id 房源id,
                c.city_name 城市,
                u.realname 投资人姓名,
                lo.room_num 居室,
                YEAR(ce.lease_date_end)-YEAR(ce.lease_date_begin) 合同期限,
                SUBSTRING_INDEX(CONCAT(ce.rent_grow_ratio,',0'),',', 1) 第一年,
                REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(ce.rent_grow_ratio,',',2)),',',1)) 第二年,
                REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(ce.rent_grow_ratio,',',3)),',',1)) 第三年,
                REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(ce.rent_grow_ratio,',',4)),',',1)) 第四年,
                REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(CONCAT(ce.rent_grow_ratio,',0'),',',5)),',',1)) 第五年,
                ce.agency_fee/100 中介费,
                ce.property_fee/100/12 物业费,
                ce.heating_fee/100/12 取暖费,
                ce.net_fee/100/12 网费,
                ce.forecast_cycle 回报周期,
                ce.day_price 已签约日价,
                inv.shop_day_price/100 测算日价
                FROM invest_compact_house ce
                LEFT JOIN invest_compact_estimate inv ON ce.house_id=inv.house_id
                LEFT JOIN city c ON ce.`city_id`=c.`id`
                LEFT JOIN users u ON ce.invest_id=u.id
                LEFT JOIN lod_house lo ON ce.house_id=lo.id
                WHERE ce.`state`=10
                AND ce.`city_id`<>376
                AND  lo.id NOT in (250780301440089343,253299308349128492)
                -- and SUBSTRING_INDEX(CONCAT(ce.rent_grow_ratio,',0'),',', 1) is not null
                -- and u.realname is not null
                )aa
                -- where aa.房屋id=247084194319071428
                ORDER BY aa.合同期限
        )n ON m.house_id=n.house_id
                GROUP BY m.city_id,m.sid,m.invest_id

    '''


def getTarget_Each(st, month_range, ri, dt, et):
    return '''
    SELECT  concat(kk.city_id,'') city_id, concat(kk.sid,'') sid,concat(kk.invest_id,'') invest_id,
            SUM(IF(rate_10>1,1,0) ) realcome_rent_count,
            SUM(IF(rate_15>1,1,0) ) realcome_cost_count,
            SUM(IF(rate_18>1,1,0) ) realcome_profit_count
            FROM(
            SELECT k.city_id,k.sid,k.invest_id, 
            sum(k.GMV)/((sum(k.act_days)/SUM(k.rent_days))*sum(k.target_GMV10)) rate_10,
            sum(k.GMV)/((sum(k.act_days)/SUM(k.rent_days))*sum(k.target_GMV15)) rate_15,
            sum(k.GMV)/((sum(k.act_days)/SUM(k.rent_days))*sum(k.target_GMV18)) rate_18
             FROM(
            SELECT m.city_id,m.sid,m.invest_id,m.house_id,n.rent_days,n.act_days,pp.GMV,qq.target_GMV10,qq.target_GMV15,qq.target_GMV18  FROM
            (
            -- 房屋
            SELECT h.city_id city_id,h.service_id sid,c.short_name short_name,usc.sname org_center,concat(ico.invest_id,'') invest_id ,u.realname investor ,concat(h.id,'') house_id ,concat(h.lodge_id,'') lodge_id 
                    FROM invest_compact_house ico
                    LEFT JOIN lod_house h ON ico.house_id=h.id
                    LEFT JOIN user_org_service_center usc ON usc.sid=h.service_id
                    -- LEFT JOIN view_lodge_base b ON  b.house_id=ico.house_id
                    LEFT JOIN city c ON h.city_id=c.id
                    LEFT JOIN users u ON ico.invest_id=u.id
                    WHERE h.city_id<>376
                    AND h.service_id IS NOT NULL
                    AND ico.invest_id IS NOT NULL
                    AND h.state  IN (100,110)
                    AND h.lodge_id IS  NOT  NULL 
                    AND h.lodge_id <>''
                    AND  c.id<>376
                    AND  h.id NOT in (250780301440089343,253299308349128492)
                    GROUP BY h.id
            ) m 
            LEFT JOIN
            -- 系数
             (SELECT
                      concat(lod_house_id,'') house_id, 
                      concat(create_time,'') frist_Online_time,
                      if(date(create_time)<'%s','%s',to_days(date(DATE_ADD('%s',INTERVAL 1 DAY))) - to_days(DATE(create_time))) rent_days,
                      if(date(create_time)<'%s','%s',to_days(date(DATE_ADD('%s',INTERVAL 1 DAY))) - to_days(DATE(create_time))) act_days
                FROM
                      lod_house_online_time
                WHERE

                       sales_channel_id IS NULL
                        OR sales_channel_id = 4
                        GROUP BY lod_house_id
                ORDER BY
                create_time ASC
            )n ON m.house_id=n.house_id
            LEFT JOIN 
            (
            -- 收益 
            SELECT concat(p.lod_house_id,'') house_id,SUM(p.`间夜`*p.price) GMV FROM(
                            SELECT m.order_id,CONCAT(m.house_id) lod_house_id, (TO_DAYS(m.`离店时间`)-TO_DAYS(m.`入住时间`))*m.bookroom_num 间夜 ,n.price FROM(
                            SELECT b.order_id,b.house_id,if(date(b.checkin_day)<='%s' AND date(b.checkout_day)>='%s' ,
                                                        '%s',if(date(b.checkin_day)>='%s' AND date(b.checkin_day)<='%s',date(b.checkin_day),0) ) 入住时间
                                                    ,if(DATE(b.checkout_day)>'%s' AND DATE(b.checkout_day)<='%s',DATE(b.checkout_day),IF(DATE(b.checkout_day)>'%s',
                                                        date_sub('%s',interval -1 day),0)  ) 离店时间,b.bookroom_num
                                                    FROM view_book_order b
                                                    WHERE  b.pay_state BETWEEN 0 AND 1
                                                    AND b.confirm_status=1
                                                    and b.cancel_status=0
                                                    AND b.city_id<>376
                                                    AND b.brush_status<>1
                                                    AND date(b.checkout_day)>'%s'  AND date(b.checkin_day)<='%s'
                            )m
                            LEFT JOIN 
                                            (SELECT b1.order_id,b1.real_income/100/((TO_DAYS(b1.checkout_day)-TO_DAYS(b1.checkin_day))*b1.bookroom_num) price FROM view_book_order b1
                                             WHERE b1.city_id<>376
                                             and b1.pay_state BETWEEN 0 AND 1
                                             AND b1.confirm_status=1
                                             and b1.cancel_status=0
                                             AND b1.brush_status<>1
                                             )n ON m.order_id=n.order_id
                            )p
                            GROUP BY p.lod_house_id
            )pp  ON m.house_id=pp.house_id
            LEFT JOIN 
            (
            -- 目标
             SELECT
                    CONCAT(aa.房屋id,'') house_id,
                    -- aa.房源id,
                    -- aa.城市,
                    -- aa.投资人姓名,
                    -- aa.居室,
                    -- aa.合同期限,
                    ROUND(aa.第一年+物业费+取暖费+网费,2) target_GMV10, ROUND(aa.第一年+物业费+取暖费+网费,2)*1.5 'target_GMV15',
                    ROUND(aa.第一年+物业费+取暖费+网费,2)*2.33 'target_GMV18'
                    -- FORMAT(aa.第二年+物业费+取暖费+网费,2) 第二年,
                    -- FORMAT(aa.第三年+物业费+取暖费+网费,2) 第三年,
                    -- FORMAT(aa.第四年+物业费+取暖费+网费,2) 第四年,
                    -- FORMAT(IF(aa.合同期限>=5,aa.第五年+物业费+取暖费+网费,0),2) 第五年,
                    -- FORMAT(IF(aa.已签约日价>0,aa.已签约日价,aa.测算日价),0) 日价,
                    -- aa.回报周期
                    FROM(
                    SELECT 
                    ce.house_id 房屋id,
                    lo.lodge_id 房源id,
                    c.city_name 城市,
                    u.realname 投资人姓名,
                    lo.room_num 居室,
                    YEAR(ce.lease_date_end)-YEAR(ce.lease_date_begin) 合同期限,
                    SUBSTRING_INDEX(CONCAT(ce.rent_grow_ratio,',0'),',', 1) 第一年,
                    REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(ce.rent_grow_ratio,',',2)),',',1)) 第二年,
                    REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(ce.rent_grow_ratio,',',3)),',',1)) 第三年,
                    REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(ce.rent_grow_ratio,',',4)),',',1)) 第四年,
                    REVERSE(SUBSTRING_INDEX (REVERSE(SUBSTRING_INDEX(CONCAT(ce.rent_grow_ratio,',0'),',',5)),',',1)) 第五年,
                    ce.agency_fee/100 中介费,
                    ce.property_fee/100/12 物业费,
                    ce.heating_fee/100/12 取暖费,
                    ce.net_fee/100/12 网费,
                    ce.forecast_cycle 回报周期,
                    ce.day_price 已签约日价,
                    inv.shop_day_price/100 测算日价
                    FROM invest_compact_house ce
                    LEFT JOIN invest_compact_estimate inv ON ce.house_id=inv.house_id
                    LEFT JOIN city c ON ce.`city_id`=c.`id`
                    LEFT JOIN users u ON ce.invest_id=u.id
                    LEFT JOIN lod_house lo ON ce.house_id=lo.id
                    WHERE ce.`state`=10
                    AND ce.`city_id`<>376
                    -- and SUBSTRING_INDEX(CONCAT(ce.rent_grow_ratio,',0'),',', 1) is not null
                    -- and u.realname is not null
                    )aa
                    -- where aa.房屋id=247084194319071428
                    ORDER BY aa.合同期限
            )qq ON m.house_id=qq.house_id
            GROUP BY m.house_id
            )k
            GROUP BY k.house_id
            )kk
            GROUP BY kk.city_id,kk.sid,kk.invest_id
    ''' % (st, month_range, et, st, ri, dt, st, st, st, st, dt, st, dt, dt, dt, st, dt)


# 入住指南
def Guide_detail():
    return '''
        SELECT c.short_name 城市,usc.sname 服务中心,CONCAT(h.id,'') 房屋id,h.address 房屋地址  ,CONCAT(lho.create_time,'') 上线时间,a.guide_url 入住指南
        FROM lod_house h
        LEFT JOIN city c ON h.city_id=c.id
        LEFT JOIN user_org_service_center usc ON h.service_id=usc.sid
        LEFT JOIN (
        SELECT
                        lod_house_id,create_time
                        FROM
                            lod_house_online_time
                        WHERE

                            sales_channel_id IS NULL
                            OR sales_channel_id = 4
                        GROUP BY lod_house_id
                        ORDER BY
                        create_time ASC
                        ) lho ON lho.lod_house_id=h.id
        LEFT JOIN lod_house_attach a ON h.id=a.lod_house_id
        WHERE h.city_id<>376
        AND h.service_id IS NOT NULL
            AND h.lodge_id IS NOT NULL
        AND h.lodge_id in (

                    SELECT l.id FROM lodge l
            WHERE l.state=0 
                    AND l.state_super = 10
                    AND l.city_id<>376 
       )

        GROUP BY h.id
    '''


# 对应线下销售
def getXxxs():
    return '''
    SELECT   
        CONCAT(h.id,'') 房屋id ,GROUP_CONCAT( DISTINCT    users.realname ORDER BY users.realname DESC) 线下管家
        FROM lod_house h
        JOIN lodge l ON h.lodge_id=l.id
        JOIN city c ON h.city_id=c.id
        JOIN user_org org ON h.work_id=org.id
        JOIN user_org_role role ON l.work_id=role.org_id
        JOIN users ON role.user_id=users.id
        where
        h.city_id<>376 
        AND role.role_id = 6
        AND role.is_delete=0
        AND h.lodge_id is NOT NULL
        and h.lodge_id<>0

    GROUP BY h.id
    '''


# 管家核心指标
def sql21_():
    return '''
                   SELECT pp.`城市`,pp.`服务中心`,pp.`房屋数`,qq.amount 上传数,  CONCAT(ROUND(qq.amount/pp.`房屋数`*100,2),'%') 入住指南上传完成率 FROM(
                    SELECT c.short_name 城市, usc.sname 服务中心,COUNT(0)  房屋数 FROM lod_house h
                            LEFT JOIN city c ON h.city_id=c.id
                            LEFT JOIN user_org_service_center usc ON h.service_id=usc.sid
                            WHERE h.city_id<>376
                            AND h.service_id IS NOT NULL
                            AND h.lodge_id is NOT NULL
                            AND h.lodge_id IN (
                            SELECT l.id
                            from lodge l 
                            WHERE l.state=0 
                            AND l.state_super = 10
                            AND l.city_id<>376

                            )
                            GROUP BY h.city_id,h.service_id
            ) pp 

            LEFT JOIN (



              SELECT m.short_name,m.sname,COUNT(0)  amount FROM (
              SELECT a.lod_house_id,h.lodge_id,c.short_name,usc.sname,a.guide_url FROM lod_house_attach a
              LEFT JOIN lod_house h ON a.lod_house_id=h.id
              LEFT JOIN city c ON c.id=h.city_id
              LEFT JOIN user_org_service_center usc ON usc.sid=h.service_id
             WHERE h.service_id IS not NULL
             AND h.city_id<>376
             AND a.guide_url IS NOT NULL
             AND a.guide_url <>''
             AND h.lodge_id IS NOT NULL
            AND h.lodge_id in(
            SELECT l.id
                            from lodge l 
                            WHERE l.state=0 
                            AND l.state_super = 10
                            AND l.city_id<>376

            )
             GROUP BY h.id
            ) m
            GROUP BY m.short_name,m.sname

            ) qq ON pp.`城市`=qq.short_name AND pp.`服务中心`=qq.sname

    '''


def sql22_(st, et):
    return '''
        SELECT m.short_name 城市,m.sname 服务中心,
        concat(round(truncate((sum(IF(m.shi<12 and date(m.create_time)>='%s' and date(m.create_time) <'%s',1,0))/sum(if(date(m.create_time)>='%s' and date(m.create_time) <'%s', 1, 0))),4)*100,2),'%%') 预抵离录入率
        FROM(
        SELECT b.create_time, c.id city_id, c.short_name,b.id,HOUR(TIMEDIFF(f.create_time,b.create_time)) shi,h.service_id,usc.sname
        FROM orders b
        JOIN order_flow f ON b.id=f.order_id
        LEFT JOIN city c ON b.city_id=c.id
        LEFT JOIN youjia.lod_house h ON b.lodgeunit_id=h.lodge_id
        LEFT JOIN youjia.user_org_service_center usc ON usc.sid=h.service_id
        WHERE date(b.create_time) BETWEEN '%s' AND '%s'
        AND b.pay_state=1
        AND b.confirm_status=1
        AND b.cancel_status=0
        AND f.operate_method='updatePreArrivalOrder_firstSetPreArrival'
        AND b.city_id <> 376
        AND b.brush_status<>1
        and b.order_channel in (1,3,20)
        AND h.service_id IS NOT NULL
        and b.create_time > '2018-08-14'
        GROUP BY b.id
        ) m
        GROUP BY m.city_id,m.service_id
    ''' % (st, et, st, et, st, et)


def sql23_(st, et):
    return '''
         select city.short_name 城市,usc.sname 服务中心,
            SUM(if ( bo.order_channel in (1,3,20,4),1,0)) 离店订单数,
            sum(if (ct.content is not null  and bo.order_channel in (1,3,20,4),1,0))  评论数,
            sum(if (ct.score < 4 and bo.order_channel in (1,3,20,4),1,0))  渠道差评数,
            sum(if (ct.score < 5 and ct.score >= 4 and  bo.order_channel in (1,3,20,4),1,0))  渠道中评数,
            sum(if (ct.score >= 5 AND ct.comment_type=0 and bo.order_channel in (1,3,20,4),1,0))  渠道好评数,
            concat(round(sum(if (ct.score >= 5 and bo.order_channel in (1,3,20,4),1,0))/ SUM(if ( bo.order_channel in (1,3,20,4),1,0))*100,2),'%%') 好评率,
            concat(round(sum(if (ct.content is not null  and bo.order_channel in (1,3,20,4),1,0)) / SUM(if ( bo.order_channel in (1,3,20,4),1,0))*100,2),'%%') 评论率
            from youjia.orders bo
            left join youjia.comment ct on ct.order_id = bo.id AND ct.comment_type=0
			LEFT JOIN youjia.order_extend d ON bo.id=d.order_id
            left join youjia.city on city.id = bo.city_id
            LEFT JOIN youjia.users u ON d.off_manager_id=u.id
            LEFT JOIN youjia.lod_house h ON h.lodge_id=bo.lodgeunit_id
            LEFT JOIN youjia.user_org_service_center usc ON usc.sid=h.service_id
            LEFT JOIN youjia.lodge l ON bo.lodgeunit_id=l.id
            where bo.city_id <> 376
            and bo.pay_state = 1
            and bo.confirm_status=1
            AND bo.cancel_status=0 and bo.brush_status<>1
            AND bo.order_type in (0,2)
            and bo.order_channel in (3,20,1,4)
            and date(bo.checkout_day) >= '%s'
            and date(bo.checkout_day) <= '%s'
            AND h.service_id IS NOT NULL
            AND d.account_mobile  REGEXP "^[1][345678][0-9]{9}$"
            AND bo.id NOT IN (

            SELECT b.order_id FROM youjia.order_extend b WHERE b.tenant_mobile LIKE '%%转%%'
                )

            group by bo.city_id,h.service_id
    ''' % (st, et)


# 差评明细
def get_bad_comment():
    """
    获取差评明细
    :return: 城市、服务中心、房源ID、房源地址、房客电话、房客姓名、订单ID、渠道、评论时间、总分、房屋卫生、管家服务、交通位置、室内设施、周边环境、评论详情
    """
    sql = """ select city.short_name 城市,
        org3.name 服务中心,
        concat(h.id,'') 房屋id,
        h.address 地址,
        d.account_mobile 房客电话,
        d.tenant_name 房客姓名,
        concat( bo.id,'') 订单ID ,
        sc.name 渠道,
        ct.create_time 评论时间, 
        ct.score 总分,
        ct.sanitation_s 房屋卫生,
        ct.service_s 管家服务,
        ct.traffic_s 交通位置,
        ct.facilities_s 室内设施,
        ct.environment_s 周边环境,
        ct.content 评论详情 
        from youjia.orders bo 
        left join youjia.comment ct on ct.order_id = bo.id  AND ct.comment_type=0
        LEFT JOIN youjia.order_extend d ON bo.id=d.order_id
        left join youjia.city on city.id = bo.city_id
        LEFT JOIN youjia.users u ON d.off_manager_id=u.id
        LEFT JOIN youjia.lod_house h ON h.lodge_id=bo.lodgeunit_id
        LEFT JOIN youjia.user_org org ON org.id=h.work_id         
        left join youjia.user_org org3 on org3.id = h.service_id
        LEFT JOIN youjia.lodge l ON bo.lodgeunit_id=l.id
        left join youjia_common.`sales_channel` sc on sc.id = bo.order_channel
        left join youjia.orders_distribute ods on ods.order_id = bo.id
        where bo.city_id <> 376 
        and bo.pay_state=1
        AND bo.confirm_status=1
        AND bo.cancel_status=0
        AND bo.order_type in (0,2)
        and ods.dis_sec_channel is null
        and bo.order_channel in (3,20,1,4)
        AND DATE(bo.checkout_day)
        BETWEEN DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 DAY),interval -day(DATE_SUB(CURDATE(), INTERVAL 1 DAY))+1 day) 
        AND  DATE_SUB(CURDATE(), INTERVAL 1 DAY)
        AND d.account_mobile  REGEXP "^[1][345678][0-9]{9}$"
        AND bo.id NOT IN (
        SELECT b.order_id FROM youjia.order_extend b WHERE b.tenant_mobile LIKE '%%转%%'
            )
        and ct.score < 4
        and ct.score <> ''
        and ct.score is not null
        group by bo.id;"""
    return sql


def good_comment():
    sql = """ select city.short_name 城市,
        org3.name 服务中心,
        concat(h.id,'') 房屋id,
        h.address 地址,
        concat( bo.id,'') 订单ID ,
        sc.name 渠道,
        
        concat(bo.create_time,'') 创建时间, concat(bo.checkin_day,'') 入住时间,concat(bo.checkout_day,'') 离店时间,
        ct.create_time 评论时间, 
        ct.score 总分,
        d.tenant_name 房客姓名
        from youjia.orders bo 
        left join youjia.comment ct on ct.order_id = bo.id  AND ct.comment_type=0
        LEFT JOIN youjia.order_extend d ON bo.id=d.order_id
        left join youjia.orders_distribute ods on ods.order_id = bo.id
        left join youjia.city on city.id = bo.city_id
        LEFT JOIN youjia.users u ON d.off_manager_id=u.id
        LEFT JOIN youjia.lod_house h ON h.lodge_id=bo.lodgeunit_id
        LEFT JOIN youjia.user_org org ON org.id=h.work_id         
        left join youjia.user_org org3 on org3.id = h.service_id
        LEFT JOIN youjia.lodge l ON bo.lodgeunit_id=l.id
        left join youjia_common.`sales_channel` sc on sc.id = bo.order_channel
        where bo.city_id <> 376 
        and bo.pay_state=1
        AND bo.confirm_status=1
        AND bo.cancel_status=0
        AND bo.order_type in (0,2)
        and ods.dis_sec_channel is null
        and bo.brush_status <> 1
        and bo.order_channel in (3,20,1,4)
        AND DATE(bo.checkout_day)
        BETWEEN DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 DAY),interval -day(DATE_SUB(CURDATE(), INTERVAL 1 DAY))+1 day) 
        AND  DATE_SUB(CURDATE(), INTERVAL 1 DAY)
        AND d.account_mobile  REGEXP "^[1][345678][0-9]{9}$"
        AND bo.id NOT IN (
        SELECT b.order_id FROM youjia.order_extend b WHERE b.tenant_mobile LIKE '%%转%%'
            )
        and ct.score >= 5
        and ct.score <> ''
        and ct.score is not null
        group by bo.id;"""
    return sql


def no_comment():
    sql = """select city.short_name 城市,
            org3.name 服务中心,
            concat(h.id,'') 房屋id,
            h.address 地址,
            d.account_mobile 房客电话,
            d.tenant_name 房客姓名,
            concat( bo.id,'') 订单ID ,
            sc.name 渠道,
            concat(bo.checkout_day,'') 离店时间
            from youjia.orders bo 
            left join youjia.comment ct on ct.order_id = bo.id  AND ct.comment_type=0
            LEFT JOIN youjia.order_extend d ON bo.id=d.order_id
            left join youjia.orders_distribute ods on ods.order_id = bo.id
            left join youjia.city on city.id = bo.city_id
            LEFT JOIN youjia.users u ON d.off_manager_id=u.id
            LEFT JOIN youjia.lod_house h ON h.lodge_id=bo.lodgeunit_id
            LEFT JOIN youjia.user_org org ON org.id=h.work_id         
            left join youjia.user_org org3 on org3.id = h.service_id
            LEFT JOIN youjia.lodge l ON bo.lodgeunit_id=l.id
            left join youjia_common.`sales_channel` sc on sc.id = bo.order_channel
            where bo.city_id <> 376 
            and bo.pay_state=1
            AND bo.confirm_status=1
            AND bo.cancel_status=0
            AND bo.order_type in (0,2)
            and bo.brush_status <> 1
            and ods.dis_sec_channel is null

            and bo.order_channel in (3,20,1,4)
            AND DATE(bo.checkout_day)
            BETWEEN DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 DAY),interval -day(DATE_SUB(CURDATE(), INTERVAL 1 DAY))+1 day) 
            AND  DATE_SUB(CURDATE(), INTERVAL 1 DAY)
            AND d.account_mobile  REGEXP "^[1][345678][0-9]{9}$"
            AND bo.id NOT IN (
            SELECT b.order_id FROM youjia.order_extend b WHERE b.tenant_mobile LIKE '%%转%%'
                )

            and (ct.score = '' or ct.score is   null)
            group by bo.id;"""
    return sql


# 服务核心指标
def sql10_(st, et):
    return '''
          SELECT m.short_name 城市,m.org_name 账号,if (n.`月累计订单数` IS NULL,0,n.`月累计订单数`) 月累计订单数,
            concat(round(if(p.`昨日卫生好评率` IS NOT NULL,p.`昨日卫生好评率`*100,0),2),'%%') 昨日卫生好评率,
						concat(round(if(p.`昨日卫生差评率` IS NOT null,p.`昨日卫生差评率` *100,0),2),'%%') 昨日卫生差评率,
						concat(round(if(p.`月累计卫生好评率`IS NOT null,p.`月累计卫生好评率` *100,0),2),'%%') 月累计卫生好评率, 
						concat(round(if(p.`月累计卫生差评率`IS NOT null,p.`月累计卫生差评率` *100,0),2),'%%') 月累计卫生差评率 
						FROM(
            -- 城市、门店
            SELECT c.short_name,l.city_id,org.`name` org_name,l.work_id FROM lodge l
            LEFT JOIN city c ON l.city_id=c.id
            LEFT JOIN user_org org ON l.work_id=org.id
            WHERE l.city_id<>376
            GROUP BY l.city_id,l.work_id
            )m LEFT JOIN(
            -- 月累计订单数
            SELECT  b.city_id,l.work_id,COUNT(0) 月累计订单数 FROM orders b
            LEFT JOIN lodge l on l.id=b.lodgeunit_id
            WHERE b.pay_state=1
            AND b.confirm_status=1
            AND b.cancel_status=0
            AND DATE(b.checkout_day) BETWEEN '%s' AND '%s'
            AND b.city_id<>376
            AND b.brush_status<>1
            GROUP BY b.city_id,l.work_id
            ) n ON m.city_id=n.city_id AND m.work_id=n.work_id
            LEFT JOIN (
            -- 卫生
            SELECT b.city_id,l.work_id,SUM(if(t.sanitation_s=5,1,0)) 月累计卫生好评论数,       SUM(if(t.sanitation_s<4,1,0)) 月累计卫生差评论数,
            COUNT(0) 月累计评论数,     SUM(if(t.sanitation_s=5,1,0))/COUNT(0) 月累计卫生好评率,SUM(if(t.sanitation_s<4,1,0))/COUNT(0) 月累计卫生差评率,
            SUM(if(t.sanitation_s=5 AND DATE(t.create_time)='%s',1,0)) 昨日累计卫生好评论数,
            SUM(if(t.sanitation_s<4 AND DATE(t.create_time)='%s',1,0)) 昨日累计卫生差评论数,
            SUM(if( DATE(t.create_time)='%s',1,0)) 昨日累计卫生评论数,
            SUM(if(t.sanitation_s=5 AND DATE(t.create_time)='%s',1,0))/SUM(if( DATE(t.create_time)='%s',1,0)) 昨日卫生好评率,
            SUM(if(t.sanitation_s<4 AND DATE(t.create_time)='%s',1,0))/SUM(if( DATE(t.create_time)='%s',1,0)) 昨日卫生差评率
            FROM `comment` t
            LEFT JOIN orders b ON t.order_id=b.id
            LEFT JOIN lodge l ON b.lodgeunit_id=l.id
            WHERE DATE(t.create_time) BETWEEN '%s' AND '%s'
            AND b.city_id<>376
            AND b.brush_status<>1
            GROUP BY b.city_id,l.work_id
            ) p ON m.city_id=p.city_id AND m.work_id=p.work_id;  
    ''' % (st, et, et, et, et, et, et, et, et, st, et)


def sql11_(st, et):
    return '''
        select
        city.short_name 城市,uo.name 账号,
        concat(round(sum(if((cs.id is not null or coa.id is not null), 1, 0))/sum(1)*100,2),'%%') 月保洁小程序使用率,
        concat(round(sum(if((cs.id is not null or coa.id is not null) and date(co.service_time)= '%s', 1, 0))/sum(if(date(co.service_time)= '%s', 1, 0))*100,2),'%%') 昨日保洁小程序使用率
        from clean_order co
        left join clean_order_aunt coa on co.id = coa.order_id and coa.id_over_clean=1
        left join clean_order_aunt coa1 on co.id = coa1.order_id and coa1.id_over_clean=1 and coa1.id<coa.id
        left join clean_sign cs on cs.order_id=co.id
        left join clean_sign cs1 on cs1.order_id=co.id and cs1.id<cs.id
        left join city on city.id = co.city_id
            left join lodge on lodge.id=co.lod_id
            left join user_org uo on uo.id=lodge.work_id
        where city.id <>376
            and date(co.service_time) BETWEEN '%s' AND '%s'
        and cs1.id is null
        and coa1.id is null
        and co.service_state BETWEEN 1 AND 4
        group by uo.id
    ''' % (st, st, st, et)


def sql12_():
    return '''
        SELECT m.`城市`,m.门店 账号,count(0) 验收逾期房屋数 FROM(
        select
        concat(lh.id, '') 房屋ID,
        city.short_name 城市,org.`name` 门店,
        u1.realname 所属投资人,
        u2.realname 招商经理,
        u3.realname BD,
        lh.address 房屋地址,
        uosc.sname 对应服务中心,
        lh.room_num 居室,
        u4.realname 业主姓名,
        concat(u4.mobile,'') 业主手机号,
        concat(ich.lease_date_begin, ' - ', ich.lease_date_end) 房屋承租日期,
        case lh.state when 0 then '房屋待签约' when 1 then '加盟失败' when 2 then '房屋筹备阶段失败' when 10 then '筹建中' when 20 then '待合并处理' when 30 then '销售准备上线中' when 100 then '已上线' when 110 then '已下线' when 120 then '违约下线' when 130 then '合同到期' else '' end 房屋状态,
        concat(ic.date_sign, '') 租房合同创建时间,
        concat(lo.create_time, '') 交付筹建时间,
        concat(lht.notice_check_time, '') 通知验收时间,
        concat(lo2.create_time, '') 验收通过时间,
        concat(ic.date_sign,'') 投资人签约时间    ,
        concat(date_add(ic.date_sign, interval 60 day),'') `签约时间+60天`,
        if(date_add(ic.date_sign, interval 60 day) < curdate(), 'fee7e7', '') tmp_bg_color
        from invest_compact_house ich
        left join invest_compact ic on ic.id=ich.invest_compact_id
        left join lod_house lh on lh.id=ich.house_id
        left join users u1 on u1.id=ic.invest_id
        left join users u2 on u2.id=ic.business_id
        left join users u3 on u3.id=ich.business_id
        left join users u4 on u4.id=ich.landlord_id
        left join user_org_service_center uosc on uosc.sid=lh.service_id
        left join lod_house_transfer lpo on lpo.house_id = lh.id
        left join city on city.id=lh.city_id
        left join lod_operations lo on lo.house_id=lh.id and lo.ops_type=1003 
        left join lod_operations lo1 on lo1.house_id=lh.id and lo1.ops_type=1003  and lo1.id<lo.id
        left join lod_house_transfer lht on lht.house_id=lh.id
        left join lod_house_transfer lht2 on lht2.house_id=lh.id and lht2.id < lht.id
        left join lod_operations lo2 on lo2.house_id=lh.id and lo2.ops_type in (1004,1005) and lht.transfer_state = 140
        left join lod_operations lo3 on lo3.house_id=lh.id and lo3.ops_type in (1004,1005) and lht.transfer_state = 140  and lo3.id>lo2.id
        LEFT JOIN user_org org ON lh.work_id=org.id
        where city.id not in (376, 305)
        and lo1.id is null 
        and lo3.id is null
        and lht2.id is null
        and lh.state < 100
        and date_add(ic.date_sign, interval 60 day) < date_add(curdate(), interval 7 day)
        GROUP BY lh.id
        order by city.pinyin
        )m
        GROUP BY m.`城市`,m.门店
    '''


# 驳回次数（300）
def sql13_():
    return '''
        select 
        city.short_name 城市,org.`name` 账号,
        sum(if(ich.state=20, 1, 0)) 驳回次数
        from invest_compact_house ich
        left join lod_house lh on lh.id=ich.house_id
        left join lodge on lodge.id=lh.lodge_id
        left join lod_house_transfer lpo on lpo.house_id = lh.id
        left join lod_house_online_time lhot on lhot.lod_house_id=lh.id
        left join lod_house_online_time lhot1 on lhot1.lod_house_id=lh.id and lhot1.id<lhot.id
        left join lod_operations lo on lo.house_id=lh.id and lo.ops_type=1003 
        left join lod_operations lo1 on lo1.house_id=lh.id and lo1.ops_type=1003  and lo1.id<lo.id
        left join lod_operations lo2 on lo2.house_id=lh.id and lo2.ops_type in (1004,1005)
        left join lod_operations lo3 on lo3.house_id=lh.id and lo3.ops_type in (1004,1005) and lo3.id>lo2.id
        left join clean_order co on co.lod_house_id=lh.id and co.service_state=4
        left join clean_order co1 on co1.lod_house_id=lh.id and co1.service_state=4 and co1.id<co.id
        left join city on city.id=lh.city_id
        LEFT JOIN user_org org ON org.id=lh.work_id
        where lo1.id is null
        and lo3.id is null
        and lhot1.id is null
        and co1.id is null
        and city.id not in (376,305)
        group by city.id with rollup
    '''


def sql14_(st, et, days):
    return '''
        SELECT m.`城市`,m.`门店` 账号,concat(round((m.total-if(n.amount is not null,n.amount,0))/m.total*100,2),'%%')  月可售率 from(
        SELECT c.short_name 城市,org.`name` 门店,sum( if(date(lho.create_time)<'%s','%s',to_days(date(DATE_ADD('%s',INTERVAL 1 DAY))) - to_days(DATE(lho.create_time)))) total  FROM
                (SELECT
                lod_house_id,create_time
                FROM
                    lod_house_online_time
                WHERE

                    sales_channel_id IS NULL
                    OR sales_channel_id = 4
                GROUP BY lod_house_id
                ORDER BY
                create_time ASC
                ) lho
                LEFT JOIN lod_house h ON lho.lod_house_id=h.id
                LEFT JOIN lodge l ON  h.lodge_id=l.id
                LEFT JOIN city c ON l.city_id=c.id
                LEFT JOIN user_org org ON l.work_id=org.id
                WHERE  l.state=0 
                AND l.state_super = 10
                AND l.city_id<>376
                AND date(lho.create_time) <= '%s' 
                GROUP BY l.city_id,l.work_id
        )m
        LEFT JOIN (

        SELECT q.`城市`,q.`门店`,if(COUNT(0) is not null,COUNT(0),0) amount FROM(
                SELECT 
                aa.房屋id,
                aa.房源id,
                aa.城市,
                aa.不可租日期,
                DATE(aa.操作时间) 操作时间,
                aa.操作人,
                aa.不可租原因,
                aa.`门店`
                FROM(
                    SELECT
                     lh.id 



                 AS '房屋id',
                     lh.title_sub AS '房屋简称',
                     lh.lodge_id AS '房源id',
                     (SELECT l.title FROM lodge l WHERE l.id = lh.lodge_id) AS '房源标题',
                     (SELECT l.channel FROM lodge l WHERE l.id = lh.lodge_id) AS channelId,
                     (CASE WHEN (SELECT l.channel FROM lodge l WHERE l.id = lh.lodge_id) = 1 THEN '木西' ELSE '非木西' END) AS '房屋来源',
                      lh.city_id AS 城市编号,
                      (SELECT  c.short_name  FROM  city c  WHERE c.id = lh.city_id )  '城市',
                       lh.work_id,org.`name` 门店,
                            (SELECT u.realname FROM users u WHERE u.id = (SELECT uog.user_id FROM user_org_role uog WHERE uog.org_id = lh.work_id AND uog.role_id = 5 LIMIT 1)) AS '线上管家',
                            (SELECT u.realname FROM users u WHERE u.id = (SELECT uog.user_id FROM user_org_role uog WHERE uog.org_id = lh.work_id AND uog.role_id = 6 LIMIT 1)) AS '线下管家',
                            lhs.dt AS '不可租日期',
                            case when lhs.modified_time is not null then lhs.modified_time else lhs.create_time end as '操作时间',
                            case when lhs.modifier_id is not null then lhs.modifier_id else lhs.creator_id end  as '操作人id',
                            (SELECT u.realname FROM users u WHERE u.id = (case when lhs.modifier_id is not null then lhs.modifier_id else lhs.creator_id end )) AS '操作人',
                            lhs.remark as '不可租原因'
                        FROM lod_house lh 
                                LEFT JOIN lod_house_stock lhs ON lh.id 



                 = lhs.house_id 
                       LEFT JOIN user_org org ON lh.work_id=org.id 

                        WHERE 
                            lh.state = 100
                        AND lh.city_id <> 376
                        AND lhs.state = 0  
                        AND lhs.dt >= '%s' AND  lhs.dt<='%s'    ## 不可租的日期
                )aa
                ORDER BY aa.操作时间,aa.不可租日期
                ) q
                GROUP BY q.`城市`,q.`门店`




        )n on m.`城市`=n.`城市` AND m.`门店`=n.`门店`
    ''' % (st, days, et, et, st, et)


def sql15_():
    return '''
        SELECT c.short_name 城市,org.name 账号,COUNT(0) 已上线门锁离线数量
        FROM lod_house h
        LEFT JOIN city c ON h.city_id=c.id
        LEFT JOIN lod_lock_error e ON h.id=e.house_id
        LEFT JOIN user_org org on org.id=h.work_id
        WHERE h.city_id<>376
        AND e.lock_state=0
        GROUP BY h.city_id,h.work_id
    '''


def sql16_():
    return '''
        SELECT c.short_name 城市,org.name 账号,COUNT(0) '门锁电量低于百分之三十的数量'
        FROM lod_house h
        LEFT JOIN city c ON h.city_id=c.id
        LEFT JOIN lod_lock_error e ON h.id=e.house_id
        LEFT JOIN user_org org on org.id=h.work_id
        WHERE h.city_id<>376
        AND e.power<30
        GROUP BY h.city_id,h.work_id


    '''


# 对应线上售
def getOnline_xs():
    return '''
    SELECT   
        CONCAT(h.id,'') 房屋id ,GROUP_CONCAT(DISTINCT     users.realname   ORDER BY users.realname DESC )  线上销售
        FROM lod_house h
        JOIN city c ON h.city_id=c.id
        JOIN user_org org ON h.work_id=org.id
        JOIN user_org_role role ON h.work_id=role.org_id
        JOIN users ON role.user_id=users.id
        where
        h.city_id<>376 
        AND role.role_id = 5
        AND role.is_delete=0
        AND h.lodge_id is NOT NULL
        and h.lodge_id<>0

    GROUP BY h.id
    '''


# 订单评论明细
def order_comment_detail():
    return '''
    SELECT c.short_name 城市,usc.sname 服务中心,CONCAT(b.id,'') 订单id,sc.`name` 渠道,CONCAT(b.checkout_day,'') 离店时间,
        CONCAT(d.house_id,'') 房屋id,
        h.title 房屋标题,h.address 地址,COUNT(0) 离店订单数,t.score 总分,
        SUM(IF(t.score IS NOT null AND t.score>=5,1,0)) 好评数,
        SUM(IF(t.score IS NOT null AND t.score>=4 AND t.score<5,1,0)) 中评数,
        SUM(IF(t.score IS NOT null AND t.score<4,1,0)) 差评数,
        SUM(IF(t.score IS  null ,1,0)) 未评论数
        FROM orders b
        LEFT JOIN order_detail d ON b.id=d.order_id
        LEFT JOIN youjia.order_extend e ON b.id=e.order_id
        LEFT JOIN lod_house h ON d.house_id=h.id
        LEFT JOIN city c ON c.id=h.city_id
        LEFT JOIN user_org_service_center usc ON h.service_id=usc.sid
        LEFT JOIN youjia_common.sales_channel sc ON sc.id=b.order_channel
        LEFT JOIN youjia.`comment` t ON b.id=t.order_id  AND t.comment_type=0
        WHERE 
        b.city_id <> 376
            and b.pay_state = 1
            and b.confirm_status=1
            AND b.cancel_status=0 and b.brush_status<>1
            AND b.order_type in (0,2)
            and b.order_channel in (3,20,1,4)
            and date(b.checkout_day) BETWEEN DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 DAY),interval -day(DATE_SUB(CURDATE(), INTERVAL 1 DAY))+1 day) 
						AND  DATE_SUB(CURDATE(), INTERVAL 1 DAY)
            AND h.service_id IS NOT NULL
            AND e.account_mobile  REGEXP "^[1][345678][0-9]{9}$"
            AND b.id NOT IN (

            SELECT b1.order_id FROM youjia.order_extend b1 WHERE b1.tenant_mobile LIKE '%%转%%'
                )
        GROUP BY b.id
    '''


# 分城市发送
def send_mail_orderBy_city(report_path_):
    today = datetime.date.today()
    if pub.debug():
        to_ = {'bianzhiwei@iyoujia.com': ['北京']}
        to_ = pub.mail_qz_
    else:
        to_ = {'data@iyoujia.com': ['北京']}
        to_ = pub.mail_qz_

    title_ = '店长日报-核心指标'
    text_ = '''Dear all:
    		附件为店长日报-核心指标数据，请查收！谢谢
    '''
    pub.send_city_mail(title_, text_, report_path_, to_,
                       None, None, extra_city_title='环节')


# 主方法入口
if __name__ == '__main__':
    today = datetime.date.today()
    if len(sys.argv) > 1:
        today = sys.argv[1]
        today = datetime.datetime.strptime(today, '%Y-%m-%d').date()
    dt = today - datetime.timedelta(days=1)

    year = dt.year
    month = dt.month
    ri = dt.day
    month_range = calendar.mdays[month]
    sx = month_range - ri

    if year:
        year = int(year)
    else:
        year = datetime.date.today().year

    if month:
        month = int(month)
    else:
        month = datetime.date.today().month

    # 获取当月第一天的星期和当月的总天数
    firstDayWeekDay, monthRange = calendar.monthrange(year, month)

    # 获取当月的第一天
    dt_start = datetime.date(year=year, month=month, day=1)
    dt_end = datetime.date(year=year, month=month, day=monthRange)

    db = get_config_51()
    sql_df = pd.read_sql(base_z(), con=db)
    result_df = sql_df

    sql_df2 = pd.read_sql(getTarget_Each_z(dt_start, month_range, ri, dt, dt_end), con=db)
    result_df = pd.merge(result_df, sql_df2, how='left', on=['city_id', 'sid', 'invest_id'])
    result_df = result_df.replace(np.nan, 0)

    temp = result_df.fair_num10 / result_df.lodge_num
    result_df = result_df.fillna(0)
    result_df['rate_fair10'] = temp
    result_df = result_df.fillna(0)
    result_df['rate_fair10'] = result_df.apply(lambda row: str(round(100 * row.rate_fair10)) + '%', axis=1)

    temp = result_df.fair_num15 / result_df.lodge_num
    result_df = result_df.fillna(0)
    result_df['rate_fair15'] = temp
    result_df = result_df.fillna(0)
    result_df['rate_fair15'] = result_df.apply(lambda row: str(round(100 * row.rate_fair15)) + '%', axis=1)

    temp = result_df.fair_num20 / result_df.lodge_num
    result_df = result_df.fillna(0)
    result_df['rate_fair20'] = temp
    result_df = result_df.fillna(0)
    result_df['rate_fair20'] = result_df.apply(lambda row: str(round(100 * row.rate_fair20)) + '%', axis=1)

    temp = result_df.GMV / result_df.rental
    result_df['rate_GMV'] = temp
    result_df = result_df.fillna(0)
    result_df['rate_GMV'] = result_df.apply(lambda row: str(round(100 * row.rate_GMV)) + '%' if row.rental != 0 else 0,
                                            axis=1)

    df = result_df[
        ['short_name', 'org_center', 'investor', 'GMV', 'rental', 'rate_GMV', 'lodge_num', 'fair_num10', 'rate_fair10',
         'fair_num15', 'rate_fair15', 'fair_num20', 'rate_fair20']]

    title = [u'城市', u'门店', '投资人', u'本月收益', '本月房租', '房租完成度', '房源数量', u'满足租金投资人数量', u'1倍占比', u'满足租金1.5倍投资人数量', u'1.5倍占比',
             u'满足租金2.0倍投资人数量', u'2.0倍占比']
    # xlsfile =u'/data/online/youjia_script/com/youjia/moban/city_investor_' + str(today) + '.xlsx'

    # 入住指南***
    sql_df9 = pd.read_sql(Guide_detail(), con=db)
    sql91_df = pd.read_sql(getXxxs(), con=db)
    result_df9 = pd.merge(sql_df9, sql91_df, how='left', on=['房屋id'])
    result_df9 = result_df9.replace(np.nan, 0)

    # 管家核心指标***
    sql_df21 = pd.read_sql(sql21_(), con=db)
    result_df10 = sql_df21.replace(np.nan, 0)

    sql_df22 = pd.read_sql(sql22_(dt_start, dt), con=db)
    result_df10 = pd.merge(result_df10, sql_df22, how='left', on=['城市', '服务中心'])
    result_df10 = result_df10.replace(np.nan, 0)

    sql_df23 = pd.read_sql(sql23_(dt_start, dt), con=db)
    result_df10 = pd.merge(result_df10, sql_df23, how='left', on=['城市', '服务中心'])
    result_df10 = result_df10.replace(np.nan, 0)

    # 差评明细
    bad_comment_df = pd.read_sql(get_bad_comment(), con=db)
    bad_on_manger_df = pd.read_sql(getOnline_xs(), con=db)
    bad_comment_df = pd.merge(bad_comment_df, bad_on_manger_df, how='left', on=['房屋id'])
    bad_off_manger_df = pd.read_sql(getXxxs(), con=db)
    bad_comment_df = pd.merge(bad_comment_df, bad_off_manger_df, how='left', on=['房屋id'])
    # 离店未评价
    no_comment_df = pd.read_sql(no_comment(), con=db)
    no_comment_df = pd.merge(no_comment_df, bad_on_manger_df, how='left', on=['房屋id'])
    no_comment_df = pd.merge(no_comment_df, bad_off_manger_df, how='left', on=['房屋id'])
    # 好评明细
    good_comment_df = pd.read_sql(good_comment(), con=db)
    good_comment_df = pd.merge(good_comment_df, bad_on_manger_df, how='left', on=['房屋id'])
    good_comment_df = pd.merge(good_comment_df, bad_off_manger_df, how='left', on=['房屋id'])

    # 服务核心指标
    sql80_df = pd.read_sql(sql10_(dt_start, dt), con=db)
    result_df8 = sql80_df
    result_df8 = result_df8.replace(np.nan, 0)

    sql81_df = pd.read_sql(sql11_(dt_start, dt), con=db)
    result_df8 = pd.merge(result_df8, sql81_df, how='left', on=['城市', '账号'])
    result_df8 = result_df8.replace(np.nan, 0)

    sql82_df = pd.read_sql(sql12_(), con=db)
    result_df8 = pd.merge(result_df8, sql82_df, how='left', on=['城市', '账号'])
    result_df8 = result_df8.replace(np.nan, 0)

    sql83_df = pd.read_sql(sql13_(), con=db)
    result_df8 = pd.merge(result_df8, sql83_df, how='left', on=['城市', '账号'])
    result_df8 = result_df8.replace(np.nan, 0)

    sql84_df = pd.read_sql(sql14_(dt_start, dt, ri), con=db)
    result_df8 = pd.merge(result_df8, sql84_df, how='left', on=['城市', '账号'])
    result_df8 = result_df8.replace(np.nan, 0)

    sql85_df = pd.read_sql(sql15_(), con=db)
    result_df8 = pd.merge(result_df8, sql85_df, how='left', on=['城市', '账号'])
    result_df8 = result_df8.replace(np.nan, 0)

    sql86_df = pd.read_sql(sql16_(), con=db)
    result_df8 = pd.merge(result_df8, sql86_df, how='left', on=['城市', '账号'])
    result_df8 = result_df8.replace(np.nan, 0)

    # 服务中心日报
    # xlsfile = u'd:/youjia_org_center_' + str(dt) + '.xlsx'
    xlsfile = u'/data/online/youjia_script/com/youjia/moban/youjia_org_center_' + str(today) + '.xlsx'
    fw_fy_df = pd.DataFrame(pd.read_excel(xlsfile))
    fw_fy_df = fw_fy_df[
        [u'城市', u'门店', u'在线房源量', u'新房源数量', u'老房源数量', '新房源入住率', '老房源入住率', '新房源日价', '老房源日价', '日可售率', '日GMV（含未支付）',
         '日入住率（含未支付）', '新房源入住率', '老房源入住率', '新房源日价', '老房源日价', '月可售率', '月入住率(含未支付)', '月GMV（含未支付）', '月房费收益（部分退款订单）',
         '月违约赔付收益', '平均房价（含未支付）', '月revPAR（含未支付）', '复购率', '未来14天可售率', '未来14天入住率']]
    fw_df = pd.DataFrame(fw_fy_df)
    fw_df = fw_df.applymap(str)

    # 房源日报
    # dname = u'd:/youjia_lodge_day_bj_' + dt.strftime("%Y-%m-%d") + '.xlsx'
    dname = u'/data/online/youjia_script/com/youjia/moban/youjia_lodge_day_bj_' + dt.strftime("%Y-%m-%d") + '.xls'
    yy_fy_df = pd.DataFrame(pd.read_excel(dname))
    yy_fy_df = yy_fy_df[
        [u'房屋id', u'房源id', u'渠道', u'上线时间', u'城市', u'门店', u'房屋title', u'街道', u'居室', u'面积', u'地址', u'是否投资人房子', u'日价',
         u'业主姓名', u'线上销售', u'线下管家', u'蚂蚁id', u'当日成单数', u'当日成单间夜', u'是否出售(1:出售；0：未出售)', u'是否可租(1:不可租；0：可租)', u'退款率',
         u'月可租率', u'入住率(含未支付)', u'月revPAR(含未支付)', u'平均房价（含未支付）', u'入住收益(含未支付)', u'房费收益（部分退款订单）', u'违约赔付收益', u'总收益',
         u'租金', u'时间进度', u'房租完成进度', u'回本完成进度', u'收益完成进度', u'退款数',
         u'订单数', u'月退款率', u'未来14天可售率', u'未来14天入住率']]
    fy_df = pd.DataFrame(yy_fy_df)
    fy_df = fy_df.applymap(str)

    # 销售维度（投资人）
    xlsfile = u'/data/online/youjia_script/com/youjia/moban/yy_on_manager_Investor.xlsx'
    # xlsfile = u'D:/yy_on_manager_Investor.xlsx'
    yy_fy_df = pd.DataFrame(pd.read_excel(xlsfile))
    yy_df = pd.DataFrame(yy_fy_df)

    # xlsfile = u'd:/city_investor_' + str(today) + '.xlsx'
    xlsfile = u'/data/online/youjia_script/com/youjia/moban/city_investor_' + str(today) + '.xlsx'
    yy_cy_df = pd.DataFrame(pd.read_excel(xlsfile))
    city_df = pd.DataFrame(yy_cy_df)

    # title = [u'城市', u'门店', u'投资人', u'本月房源收益', u'覆盖房租目标', u'覆盖房租收益目标完成度', u'回本收益目标', u'回本收益目标完成度', u'盈利收益目标',
    #         u'盈利收益目标完成度', u'投资人名下房源', u'覆盖房租的房源', u'覆盖房租的房源占比', u'回本的房源', u'回本的房源占比', u'盈利的房源', u'盈利占比']
    # xlsfile = u'd:/investor_' + str(dt) + '.xlsx'

    # add 所有订单评论
    all_df=pd.read_sql(order_comment_detail(), con=db)
    all_df = pd.merge(all_df, bad_on_manger_df, how='left', on=['房屋id'])
    all_df = pd.merge(all_df, bad_off_manger_df, how='left', on=['房屋id'])

    xlsfile = u'/data/online/youjia_script/com/youjia/moban/investor_' + str(today) + '.xlsx'
    with pd.ExcelWriter(xlsfile, options={'encoding': 'utf-8'}) as writer:
        df.columns = title
        city_df.to_excel(writer, sheet_name=u'城市投资人维度', index=False, encoding='utf-8')
        df.to_excel(writer, sheet_name=u'分城市门店投资人收益进度', index=False, encoding='utf-8')
        fy_df.to_excel(writer, sheet_name=u'房源日报', index=False, encoding='utf-8')
        yy_df.to_excel(writer, sheet_name=u'线上销售投资人维度', index=False, encoding='utf-8')
        result_df10.to_excel(writer, sheet_name=u'管家核心指标', index=False, encoding='utf-8')
        result_df9.to_excel(writer, sheet_name=u'入住指南', index=False, encoding='utf-8')
        bad_comment_df.to_excel(writer, sheet_name=u'差评明细', index=False, encoding='utf-8')
        good_comment_df.to_excel(writer, sheet_name=u'好评明细', index=False, encoding='utf-8')
        no_comment_df.to_excel(writer, sheet_name=u'离店未评价', index=False, encoding='utf-8')
        result_df8.to_excel(writer, sheet_name=u'服务核心指标', index=False, encoding='utf-8')
        fw_df.to_excel(writer, sheet_name=u'服务中心日报', index=False, encoding='utf-8')
        all_df.to_excel(writer, sheet_name=u'当月订单离店订单评论详情', index=False, encoding='utf-8')

        # yy_df.to_excel(writer, sheet_name=u'线上销售投资人维度', index=False, encoding='utf-8')
        writer.save()
        writer.close()
    db.close()

    mail_content = 'Dear all,\n'
    mail_content = mail_content + '    附件为店长日报-核心指标，请查收! \n'
    mail_content = mail_content + '谢谢.'
    recever = 'shenzhiqiang@iyoujia.com;yangweiwei@iyoujia.com;machao@iyoujia.com;' \
             'dt@iyoujia.com;yy@iyoujia.com;liuqi@iyoujia.com;wangjing@iyoujia.com;wangjian@iyoujia.com;' \
            'ccsj@iyoujia.com;zhuqiang@iyoujia.com;lvweixin@iyoujia.com'
    # recever = 'dt@iyoujia.com'
    sendBigEmail('店长日报-核心指标！' + '_' + dt.strftime("%Y-%m-%d"), mail_content, xlsfile, recever)
    # df= pd.DataFrame(pd.read_excel(xlsfile))
    # df1=df[['short_name','org_center']]
    # print(df1)

    send_mail_orderBy_city(xlsfile)

